
import pymysql
# useful for handling different item types with a single interface
from itemadapter import ItemAdapter


class ConsolePipeline:
    '''
        默认Pipeline 啥也不干
    '''
    def process_item(self, item, spider):
        print("="*40)
        for key in item:
            print(key + ": " + str(item[key]))
        return item

class MysqlPipeline(object):

    def open_spider(self, spider):
        host = spider.settings.get('MYSQL_HOST', '111.229.116.141')
        port = spider.settings.get('MYSQL_PORT', 3306)
        db = spider.settings.get('MYSQL_DB', 'stock_strategy')
        user = spider.settings.get('MYSQL_USER', 'yfzx')
        passwd = spider.settings.get('MYSQL_PASSWORD', 'xf@yfzx')
        charset = spider.settings.get('MYSQL_CHARSET', 'utf8')
        self.conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd, charset=charset)
        self.cursor = self.conn.cursor()

    def close_spider(self, spider):
        self.conn.commit()
        self.conn.close()

    def process_item(self, item, spider):
        self.insert_db(item)
        return item

    def insert_db(self, item):
        # 获取对象的类型名称
        class_name = type(item).__name__
        # 获取对象的所有属性和方法名称列表
        attributes_list = item.fields.keys()
        #使用Inset into 表名(字段1,字段2) values(值1,值2)进行插入,并对查看插入数据是否成功
        values = []
        sql = 'insert into '+class_name
        for attr in attributes_list:
            values.append(item[attr])
            print(item[attr])
        sql += ' (' + ','.join(attributes_list) + ') values (' + ','.join(['%s']*len(attributes_list)) + ')'
        print("="*30)
        print(sql)
        print(values)
        self.cursor.execute(sql, values)